﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using DevComponents.DotNetBar.Controls;
using KnowledgeNotebook.Tip;
using KnowledgeNotebook.Properties;

namespace KnowledgeNotebook.BaseClass
{
    public class DatabaseOptionBaseClass
    {
        private static string _server;
        public static string Server
        {
            get { return _server; }
            set { _server = value; }
        }

        private static string _database;
        public static string Database
        {
            get { return _database; }
            set { _database = value; }
        }

        private static string _userName;
        public static string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        private static string _password;
        public static string Password
        {
            get { return _password; }
            set { _password = value; }
        }

        private static bool _isTrustConnect;
        public static  bool IsTrustConnect
        {
            get { return _isTrustConnect; }
            set { _isTrustConnect = value; }
        }

        private static string _connectString;
        public static string ConnectString
        {
            get { return _connectString; }
            set { _connectString = value; }
        }


        private DataTable[] _queryResult;
        public DataTable[] QueryResult
        {
            get { return _queryResult; }
            set { _queryResult = value; }
        }

        private string _errorMsg;
        public string ErrorMsg
        {
            get { return _errorMsg; }
            set { _errorMsg = value; }
        }


        private SqlCommand _sqlCommand;
       private SqlConnection _sqlConnection;


       public DatabaseOptionBaseClass()
       {

           _sqlConnection = null;
           _sqlCommand = null;

    //       IsTrustConnect = false;

           //UserName = String.Empty;
           //Password = String.Empty;
           //Server = String.Empty;
           //Database = String.Empty;

         
       }


       protected string GetConnectionString()
       {
           string connectstring;
           if (IsTrustConnect)
           {
               connectstring = "Data Source=" + Server + ";Initial Catalog=" + Database + ";Integrated Security=true";
           }
           else
           {
               connectstring = "Data Source=" + Server + ";Initial Catalog=" + Database +
                                ";Persist Security Info=True;User ID="+UserName+";Password="+Password;
           }
           return connectstring;
       }


       private static void ShowErrorMessage(string message)
       {
           MessageBox.Show(message, Resources.ErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error);
       }


       protected DataTable SelectData(string procedurename,SqlParameter[] sqlParameters)
       {
           string connectionstring = ConnectString;
           _sqlConnection=new SqlConnection(connectionstring);
           DataSet dataSet=new DataSet();

           try
           {
               _sqlCommand = _sqlConnection.CreateCommand();
               _sqlConnection.Open();
               _sqlCommand.CommandType = CommandType.StoredProcedure;
               _sqlCommand.CommandText = procedurename;
               SqlDataAdapter sqlDataAdapter=new SqlDataAdapter();
               sqlDataAdapter.SelectCommand = _sqlCommand;

               _sqlCommand.Parameters.Clear();
               foreach (SqlParameter sqlParameter in sqlParameters)
               {
                   _sqlCommand.Parameters.Add(sqlParameter);
               }

               dataSet.Clear();
               sqlDataAdapter.Fill(dataSet);

           }
           catch (Exception ex)
           {
               ShowErrorMessage(ex.Message);
               return null;
           }
           finally
           {
               _sqlConnection.Close();
           }

           if (dataSet.Tables[0]!=null && dataSet.Tables[0].Rows.Count>0)
           {
              return dataSet.Tables[0]; 
           }
           return null;

       }


       protected int ExecuteProcedure(string procedurename,SqlParameter[] parameters)
       {
           string connectstring = ConnectString;
           _sqlConnection=new SqlConnection(connectstring);
           
           try
           {
               _sqlConnection.Open();
               _sqlCommand = _sqlConnection.CreateCommand();
               _sqlCommand.CommandType = CommandType.StoredProcedure;
               _sqlCommand.CommandText = procedurename;
               _sqlCommand.Parameters.Clear();

               foreach (SqlParameter parameter in parameters)
               {
                   _sqlCommand.Parameters.Add(parameter);
               }

               _sqlCommand.ExecuteNonQuery();
               return 1;
           }
           catch (Exception ex)
           {
               ShowErrorMessage(ex.Message);
               return 0;
           }
           finally
           {
               _sqlConnection.Close();
           }
       }


       protected void ExecuteNotSelectSql(string sql)
       {
           string connectstring = ConnectString;
           _sqlConnection=new SqlConnection(connectstring);

           try
           {
               _sqlCommand = _sqlConnection.CreateCommand();
               _sqlConnection.Open();
               _sqlCommand.CommandType = CommandType.Text;
               _sqlCommand.CommandText = sql;

               _sqlCommand.ExecuteNonQuery();

           }
           catch (Exception ex)
           {
               ShowErrorMessage(ex.Message);
           }
           finally
           {
               _sqlConnection.Close();
           }
       }


        protected DataTable ExecuteSelectSql(string sql)
        {
            string connectstring = ConnectString;
            DataSet dataSet = new DataSet();
            _sqlConnection=new SqlConnection(connectstring);
            try
            {
                _sqlCommand = _sqlConnection.CreateCommand();
                _sqlCommand.CommandType = CommandType.Text;
                _sqlCommand.CommandText = sql;
                _sqlConnection.Open();

                SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(sql,_sqlConnection);


                dataSet.Clear();
                sqlDataAdapter.Fill(dataSet);
            }
            catch(Exception ex)
            {
                ShowErrorMessage(ex.Message);
                return null;
            }
            finally
            {
                _sqlConnection.Close();
            }

            if (dataSet.Tables.Count>0 &&dataSet.Tables[0]!=null && dataSet.Tables[0].Rows.Count>0)
            {
                return dataSet.Tables[0];
            }
            return null;
        }


        public void CancelConnection()
        {
            if (_sqlConnection!=null)
            {
                _sqlConnection.Close();
                MessageBox.Show("连接已取消。");
            }
        }

        public DataTable GetSchemas()
        {
            string connectstring = ConnectString;
            DataTable dt=new DataTable();

            _sqlConnection=new SqlConnection(connectstring);
            try
            {
                _sqlConnection.Open();
                dt = _sqlConnection.GetSchema("Databases");

            }
            catch (Exception ex)
            {
                Tip.Tip.ShowErrorMsg(Resources.ErrorCaption,ex.Message);
            }
            finally
            {
                _sqlConnection.Close();
            }

            return dt;
        }

        //ArrayList al=new ArrayList();
        
        protected string ExecuteCustomSql(DataGridViewX dataGridView,string sql)
        {
            string connectstring = ConnectString;
            DataSet dataSet = new DataSet();
            _sqlConnection = new SqlConnection(connectstring);
            try
            {
                _sqlCommand = _sqlConnection.CreateCommand();
                _sqlCommand.CommandType = CommandType.Text;
                _sqlCommand.CommandText = sql;
                _sqlConnection.Open();

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, _sqlConnection);

               int row = _sqlCommand.ExecuteNonQuery();
                if (row==-1)
                {
                    dataSet.Clear();

                    row=sqlDataAdapter.Fill(dataSet);

                    if (dataSet.Tables.Count > 0 && dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
                    {
                        dataGridView.DataSource = dataSet.Tables[0];
                    }
                    
                }

                

                return "命令成功执行。影响" + row + "行。";
            }
            catch (Exception ex)
            {
               // ShowErrorMessage(ex.Message);
                ErrorMsg = ex.Message;
                return "错误："+ErrorMsg;
            }
            finally
            {
                _sqlConnection.Close();
            }


          //  return "命令成功执行。影响"+row+"行。";
        }



        protected void ExecuteCustomSql(TextBoxX txtResult,SuperTabControl tabControl, string sql)
        {
            //重置控件状态
            txtResult.ResetText();txtResult.ResetForeColor();

            for (int i = 0; i < tabControl.Tabs.Count; i++)
            {
                if (tabControl.Tabs[i].Name!="tabQueryMessage")
                {
                    tabControl.Tabs.RemoveAt(i);
                    i--;}
            }

            //查询
            string connectstring = ConnectString;
            DataSet dataSet = new DataSet();
            _sqlConnection = new SqlConnection(connectstring);
            try
            {
                _sqlCommand = _sqlConnection.CreateCommand();
                _sqlCommand.CommandType = CommandType.Text;
                _sqlCommand.CommandText = sql;
                _sqlConnection.Open();

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, _sqlConnection);

                //先执行一次查询，如果有数据返回的，就肯定是表。
                //int row = _sqlCommand.ExecuteNonQuery();
                //if (row!=-1)
                //{
                //    txtResult.ForeColor = Color.Blue;
                //    txtResult.Text += "命令成功执行。影响" + row + "行。";
                //    txtResult.Text += Environment.NewLine;
                //}
                //if (row == -1)
                //{
                    dataSet.Clear();

                    sqlDataAdapter.Fill(dataSet);

                    if (dataSet.Tables.Count > 0)
                    {

                        for (int i = 0; i < dataSet.Tables.Count; i++)
                        {
                            if (dataSet.Tables[i] !=null && dataSet.Tables[i].Rows.Count > 0)
                            {

                                SuperTabItem item = tabControl.CreateTab("结果" + (i + 1));
                                DataGridViewX gridViewX = new DataGridViewX();
                                gridViewX.Dock = DockStyle.Fill;
                                gridViewX.AllowUserToAddRows = false;
                                gridViewX.AllowUserToDeleteRows = false;
                                gridViewX.BackgroundColor = SystemColors.ControlLightLight;
                                gridViewX.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
                                gridViewX.DataSource = dataSet.Tables[i];
                                item.AttachedControl.Controls.Add(gridViewX);


                                tabControl.SelectedTabIndex = 1;
                            }

                        }


                    }

                    txtResult.ForeColor = Color.Blue;
                    txtResult.Text += "命令成功执行。";
                    txtResult.Text += Environment.NewLine;
                //}



               // return "命令成功执行。影响" + row + "行。";
            }
            catch (Exception ex)
            {
                // ShowErrorMessage(ex.Message);
                ErrorMsg = ex.Message;
                txtResult.ForeColor = Color.Red;
                txtResult.Text = "错误：";
                txtResult.Text += ErrorMsg;
                txtResult.Text += Environment.NewLine;
                tabControl.SelectedTabIndex = 0;
                // return "错误：" + ErrorMsg;
            }
            finally
            {
                _sqlConnection.Close();
            }


            //  return "命令成功执行。影响"+row+"行。";
        }
    }

    
}
